package com.mds.dmd.core.service;

import com.haulmont.cuba.core.global.DataManager;
import com.mds.dmd.entity.DBTable;
import com.mds.dmd.entity.TableColumn;
import com.mds.dmd.entity.TargetDB;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;
import org.springframework.util.Assert;
import schemacrawler.schema.Catalog;
import schemacrawler.schema.Column;
import schemacrawler.schema.Table;
import schemacrawler.schemacrawler.*;
import schemacrawler.tools.options.Config;
import schemacrawler.tools.utility.SchemaCrawlerUtility;

import javax.inject.Inject;
import java.sql.Connection;
import java.util.Optional;

@Service(PDBMetaService.NAME)
public class PDBMetaServiceBean implements PDBMetaService {
    @Inject
    protected TargetDBConnService targetDBConnService;

    @Inject
    protected DataManager dataManager;

    private Logger log= LoggerFactory.getLogger(PDBMetaServiceBean.class);

    @Override
    public void refreshCatalog(TargetDB targetDB) throws Exception {
        Assert.notNull(targetDB,"db argument can't be null!");

        TargetDB  db=dataManager.load(TargetDB.class)
                .id(targetDB.getId())
                .one();

        Catalog database=null;

        SchemaCrawlerOptions scOptions = SchemaCrawlerOptionsBuilder.newSchemaCrawlerOptions();
        String dbSchema=db.getDbSchema().toLowerCase();
        if(!StringUtils.isBlank(dbSchema)){
            scOptions=scOptions
                    .withLimitOptions(
                        LimitOptionsBuilder.builder()
                            .includeSchemas( s -> s.toLowerCase().equals(dbSchema)).toOptions()
                    )
                    .withLoadOptions(
                            LoadOptionsBuilder.builder()
                                    .withInfoLevel(InfoLevel.standard)
                                    .toOptions()
                    );
        }

        SchemaRetrievalOptions srOptions=SchemaRetrievalOptionsBuilder.builder()
                .with(SchemaInfoMetadataRetrievalStrategy.indexesRetrievalStrategy,MetadataRetrievalStrategy.none)
                .with(SchemaInfoMetadataRetrievalStrategy.foreignKeysRetrievalStrategy,MetadataRetrievalStrategy.none)
                .toOptions();

        log.info("开始读取数据库元信息...");
        try(Connection conn=targetDBConnService.createConn(db)){
            database = SchemaCrawlerUtility.getCatalog(conn, srOptions,scOptions,new Config());
        }
        log.info("读取完成!");


        parseCatalog(db,database);
    }

    private void parseCatalog(TargetDB db,Catalog ct){
        if(ct!=null){
            for (Table t : ct.getTables()) {  //读取表和视图信息
                log.info("读取表："+t.getName());
                String tbName = t.getName();
                String tbComment = t.getRemarks();
                boolean hasPK = t.hasPrimaryKey();

                Optional<DBTable>  result=dataManager.load(DBTable.class)
                        .query("select e from dmd_DBTable e where e.tableName=:tbn ")
                        .parameter("tbn",tbName)
                        .view("withTargetDB")
                        .optional();

                DBTable table=null;
                if(!result.isPresent()){
                    table=new DBTable();
                }else{
                    table=result.get();
                }

                table.setComment(tbComment);
                table.setTargetDB(db);
                table.setTableName(tbName);
                table.setHasPK(hasPK);

                if(!t.getTableType().isView()){ //表
                    table.setIsView(false);
                }else{ //视图
                    table.setIsView(true);
                }

                dataManager.commit(table);

                readColumns(t,table);
            }

        }
    }

    private void readColumns(Table t,DBTable table){
        for(Column col : t.getColumns()){
            Optional<TableColumn>  result=dataManager.load(TableColumn.class)
                    .query("select e from dmd_TableColumn e where e.dbTable=:tbn and e.colName=:colName")
                    .parameter("tbn",table)
                    .parameter("colName",col.getName())
                    .optional();

            TableColumn tcol=null;
            if(!result.isPresent()){
                tcol=new TableColumn();
            }else{
                tcol=result.get();
            }

            tcol.setColName(col.getName());
            tcol.setColAlias(col.getRemarks());
            tcol.setColType(col.getColumnDataType().getDatabaseSpecificTypeName());
            tcol.setDbTable(table);
            tcol.setIsPK(col.isPartOfPrimaryKey());

            dataManager.commit(tcol);
        }
    }

}